USE [NANS21V]
GO

/****** オブジェクト: SqlProcedure [dbo].[KonseiSyukei] スクリプト日付: 2014/05/16 12:43:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KonseiSyukei]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[KonseiSyukei]
GO

CREATE PROCEDURE [dbo].[KonseiSyukei]
	@TAIKAINO int,
	@KYOGINO int,
	@SYUMOKUNO int
,@SYUMOKUKUBUN int
AS
SELECT 
	A.KYOGISYANO, 
	--dbo.Kaze(@SYUMOKUKUBUN,2,dbo.Kiroku(A.KIROKU,@SYUMOKUKUBUN,SYUMO.KIROKUKUBUN ,0),A.KAZE)AS KIROKU,
	dbo.Kaze(SYUMO.SYUMOKUKUBUN,2,dbo.Kiroku(A.KIROKU,SYUMO.SYUMOKUKUBUN,SYUMO.KIROKUKUBUN 
	,(CASE WHEN ISNUMERIC(A.KIROKUFLG)  = 1 then A.KIROKUFLG else 3 END)
	),A.KAZE)AS KIROKU,
	A.KIROKUFLG,
	A.TOKUTEN,
	A.SYUKKETU,
	A.KAZE,
	A.TOKUTENFLG ,
	SYUMO.KIROKUKUBUN, 
	SYUMO.SYUMOKUNO,
	SYUMO.SYUMOKUKUBUN, 
	RO.SUBNO

FROM 
	TBL_ROUND RO,
	TBL_KONSEI_KOBETU A 
	LEFT OUTER JOIN (
		  SELECT 
			SYUMO.KIROKUKUBUN 
			,SYUMO.SYUMOKUNO
			,SYUMO.SYUMOKUKUBUN 
		  FROM TBL_SYUMOKU_MASTER AS SYUMO --20140514
		--WHERE SYUMO.SYUMOKUNO = @SYUMOKUNO
		) AS SYUMO
		ON A.SYUMOKUNO = SYUMO.SYUMOKUNO--20140514
WHERE 
	A.TAIKAINO = @TAIKAINO 
	AND A.KYOGINO = @KYOGINO 
	AND A.SYUMOKUNO = @SYUMOKUNO
	AND RO.ROUNDNO = 6
	AND RO.KYOGINO = A.KYOGINO
	AND RO.TAIKAINO = A.TAIKAINO